-- @owner: @nanyang12
-- @date: 2025/4/1
-- @testpoint:使用postgresql风格创建自定义函数，函数带RETURNS TABLE参数，指定language为pltsql

-- 连接D库
@conn PrimaryDbDmode;
-- 创建插件
--create extension shark;
-- 创建 employees 表
CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100),
 department_id INT
);
-- 插入示例数据
INSERT INTO employees (employee_name, department_id) VALUES 
('Alice', 1), ('Bob', 1), ('Charlie', 2), ('David', 2);
--创建departments表
CREATE TABLE departments (
 department_id NUMBER(5) PRIMARY KEY,
 department_name VARCHAR2(50) NOT NULL
);
--插入示例数据
INSERT INTO departments (department_id, department_name)
VALUES
 (1, '研发部'),
 (2, '市场部'),
 (3, '财务部');
-- 创建自定义函数，函数带RETURNS TABLE参数
CREATE OR REPLACE FUNCTION get_employees_by_department(IN dept_id INT)
RETURNS TABLE (
 emp_id INT,
 emp_name VARCHAR(100),
 dept_name VARCHAR(100)
) AS $$
BEGIN
 RETURN QUERY
 SELECT 
 e.employee_id,
 e.employee_name,
 d.department_name
 FROM 
 employees e
 JOIN 
 departments d ON e.department_id = d.department_id
 WHERE 
 e.department_id = dept_id;
END;
$$ LANGUAGE 'pltsql';
/
-- 调用函数，传入部门 ID 为 2，成功
SELECT * FROM get_employees_by_department(2);
--通过系统表查询函数信息，成功
select l.lanname from pg_language l join pg_proc p on l.oid = p.prolang and p.proname in ('get_employees_by_department');
--清理环境
drop table if exists employees;
drop table if exists departments;
drop function if exists multiply_numbers;
drop function if exists get_employees_by_department;